package com.xiye.common.util;

import com.xiye.common.entity.PageData;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 西野
 * @version V1.0
 * @title FileDownload.java
 * @package com.xiye.common.util
 * @description 从EXCEL导入到数据库
 * @date 2018-07-20
 */
@SuppressWarnings({"resource", "deprecation"})
public class ObjectExcelRead {

    /*
     * @Author 西野
     * @Date 2018-07-20 16:26:11
     * @Description 从EXCEL导入到数据库
     * @Param [filepath, filename, startrow, startcol, sheetnum] filepath 文件路径;filename 文件名;startrow 开始行号;startcol 开始列号;sheetnum sheet
     * @Return java.util.List<java.lang.Object>
     */
    public static List<Object> readExcel(String filepath, String filename, int startrow, int startcol, int sheetnum) {
        List<Object> varList = new ArrayList<Object>();

        try {
            File target = new File(filepath, filename);
            FileInputStream fi = new FileInputStream(target);

            XSSFWorkbook wb = new XSSFWorkbook(fi);
            XSSFSheet sheet = wb.getSheetAt(sheetnum); // sheet 从0开始
            int rowNum = sheet.getLastRowNum() + 1; // 取得最后一行的行号

            for (int i = startrow; i < rowNum; i++) { // 行循环开始

                PageData varpd = new PageData();
                XSSFRow row = sheet.getRow(i); // 行
                int cellNum = row.getLastCellNum(); // 每行的最后一个单元格位置

                for (int j = startcol; j < cellNum; j++) { // 列循环开始

                    XSSFCell cell = row.getCell(Short.parseShort(j + ""));
                    String cellValue = null;
                    if (null != cell) {
                        switch (cell.getCellType()) { // 判断excel单元格内容的格式，并对其进行转换，以便插入数据库
                            case 0:
                                DecimalFormat df = new DecimalFormat("#");
                                /*当类型为数值型时，格式化，用以保存数据原始数值，防止数值过长转换科学计数法*/
                                cellValue = String.valueOf(df.format(cell.getNumericCellValue()));
                                break;
                            case 1:
                                cellValue = cell.getStringCellValue();
                                break;
                            case 2:
                                cellValue = cell.getNumericCellValue() + "";
                                // cellValue = String.valueOf(cell.getDateCellValue());
                                break;
                            case 3:
                                cellValue = "";
                                break;
                            case 4:
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case 5:
                                cellValue = String.valueOf(cell.getErrorCellValue());
                                break;
                        }
                    } else {
                        cellValue = "";
                    }

                    varpd.put("var" + j, cellValue);

                }
                varList.add(varpd);
            }

        } catch (Exception e) {
            System.out.println(e);
        }

        return varList;
    }
}
